package kr.hkit.BusinessManagement.DBCon;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import kr.hkit.BusinessManagement.DTO.BusinessEmpDTO;
import kr.hkit.framework.dao.CloseDAOUtil;
import kr.hkit.framework.dao.LoadPropertyDAO;

public class BusinessEmpDBCon {
	
	PreparedStatement pstmt;
	ResultSet rs;
	Connection con;
	public BusinessEmpDBCon() {
		con = LoadPropertyDAO.getconConnection();
	}

	public ArrayList<BusinessEmpDTO> selectAllEMP(){
		ArrayList<BusinessEmpDTO> empLists = new ArrayList<>();
		
		String sql = "select CODE, NAME, LEVEL from business_employee;";
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				String code = rs.getString(1);
				String name = rs.getString(2);
				String level = rs.getString(3);
				BusinessEmpDTO emp = new BusinessEmpDTO(name, code, level);
				empLists.add(emp);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			CloseDAOUtil.close(rs, pstmt);
		}
		return empLists;
	}

	public void AddBusinessEmployee(BusinessEmpDTO emp){
		String sql = "insert into business_employee values(?,?,?);";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, emp.getCode());
			pstmt.setString(2, emp.getName());
			pstmt.setString(3, emp.getLevel());
			pstmt.executeUpdate();
			JOptionPane.showMessageDialog(null, emp + "\n등록완료");
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally{
			CloseDAOUtil.close(pstmt);
		}
	}

	public BusinessEmpDTO searchEmpByCode(String empCode) {
		String sql = "select code, name, level from business_employee where code =? ;";
		String code=null, name=null, level = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, empCode);
			rs = pstmt.executeQuery();
			while(rs.next()){
				code = rs.getString(1);
				name = rs.getString(2);
				level = rs.getString(3);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(rs, pstmt);
		}
		return new BusinessEmpDTO(code, name, level);
	}

	public void deleteBusinessEmp(BusinessEmpDTO emp) {
		String sql = "delete from business_employee where code =?;";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, emp.getCode());
			pstmt.execute();
			
			JOptionPane.showMessageDialog(null, emp + "\n삭제완료");
		} catch (SQLException e) {

			e.printStackTrace();
		}finally{
			CloseDAOUtil.close(pstmt);
		}
		
		
		
	}
}
